#! /usr/bin/python
# -*- coding: cp1250 -*-
#sys.path.append(r'C:\x\y')

import glob, sys, os, re, string
import pepe.p_txt.bigtxt as bt

def main(inFile, colDelimiter="\t", hasHeader=1):
    ''' Purpose:    Define columns lenghts. Create pg table sql.
        Todo:       Guess columns type.
    '''
    out_fl = open('pg_examine_sql.out','w')
    out_h = {}
    header_l = []
    flcnt_n = 0
    for line_s in bt.lines(inFile):
    
        line_s = re.sub('\n', '', line_s)
        
        flcnt_n += 1
        columns_l = string.split(line_s[:-1], colDelimiter)
        #for index, value in enumerate(columns_l[:-1]):
        for index, value in enumerate(columns_l):
            index = 'c'+string.zfill(index+1,3)
            if  hasHeader <> 0 and flcnt_n == 1:
                header_l = columns_l
            else:
                if out_h.get(index):
                    if  out_h[index] < len(str(value)):
                        out_h[index] = len(str(value))
                else:
                    out_h[index] = len(str(value))

    sql_s = '--DROP TABLE '+inFile[:-4]+'; \nCREATE TABLE '+inFile[:-4]+ '('
    for i in sorted(out_h.keys()):
        sql_s += '\n'+str(i)+'\t\t VARCHAR('+re.sub('0','1',str(out_h[i]))+'),'
        #sql_s += '\n'+str(i)+'\t\t numeric(8,2),'
    sql_s = sql_s[:-1]+'\n);\n\n'
    out_fl.write( sql_s )

    sql_s = ''
    for i in sorted(out_h.keys()):
        #sql_s += '\n'+str(i)+'\t\t varchar('+re.sub('0','1',str(out_h[i]))+'),'
        sql_s += '\n'+str(i)+'\t\t NUMERIC(12,2),'
    sql_s = sql_s[:-1]+'\n);\n\n'
    out_fl.write( sql_s )

    #print header_l
    out_fl.write("\nCREATE TABLE '+inFile[:-4]+ '('")
    for i in sorted(out_h.keys()):
        #print i, int(i[1:])
        if hasHeader <> 0:
            out_fl.write( str(i) +'_'+ re.sub('\s{1,}', '', header_l[int(i[1:])-1]) +' VARCHAR('+str(out_h[i]) + '),\n' )
        else:
            out_fl.write( str(i) +' VARCHAR('+str(out_h[i]) + '),\n' )
    out_fl.write("\n');'")
    out_fl.write('\n\n')

    #print header_l
    for i in sorted(out_h.keys()):
        #print i, int(i[1:])
        if hasHeader <> 0:
            out_fl.write( str(i) +' as '+ header_l[int(i[1:])-1] +' LENGTH '+str(out_h[i]) + '\n' )
        else:
            out_fl.write( str(i) +' as _ COL_SPAN = '+ str(out_h[i]) + '\n' )
    out_fl.write('\n\n')

    #		'>>' as xx,
    cnt_avg_max_min_s = """
                sum(%col%) 								as %colname%,
                count(case when %col% is not Null then 1 end) 				as %colname%_cnt,
                round(sum(%col%) / count(case when %col% is not Null then 1 end),2) 	as %colname%_avg_hr,
                max(%col%) 								as %colname%_max,
                min(%col%) 								as %colname%_min,
                """
    
    for i in sorted(out_h.keys()):
        cnt_avg_max_min_2s = re.sub('%col%', i, cnt_avg_max_min_s)
        if hasHeader <> 0:        
            cnt_avg_max_min_2s = re.sub('%colname%', header_l[int(i[1:])-1], cnt_avg_max_min_2s)
        else:
            cnt_avg_max_min_2s = re.sub('%colname%', i, cnt_avg_max_min_2s)
        out_fl.write( cnt_avg_max_min_2s )
        cnt_avg_max_min_2s = ''
    out_fl.write('\n\n')

    out_fl.close()
    return 1

HELP = """Usage: csv-pgtb INP_FILE COL_DELIMITER HAS_HEADER
Purpose: Define columns lenghts. Create pg table sql.
Example: csv_pgtb test.csv \"|\" 1"""

if __name__ == "__main__":
    #print __do("test.csv", "|", 1)
    
    if len(sys.argv) == 2:
        main(sys.argv[1])
    elif len(sys.argv) == 3:
        main(sys.argv[1], sys.argv[2])
    elif len(sys.argv) == 4:
        main(sys.argv[1], sys.argv[2], sys.argv[3])
    else:
        print HELP

